package com.zy.db.odbc;

import com.zy.common.core.exception.BizException;
import com.zy.db.enums.DbErrorEnums;
import com.zy.db.odbc.Enum.FieldTypeEnum;
import com.zy.db.odbc.Enum.TableAttributeEnum;
import com.zy.db.odbc.consts.OdbcConstants;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
public class OdbcUtil {

    private static OdbcPool pool = new OdbcPool();

    public static Connection connect(OdbcConnectParam param) throws SQLException {

        Connection connection = pool.getConnection(param.getUrl());
        if (connection != null && !connection.isClosed()) {
            return connection;
        }
        return pool.getConnection(param.getUrl(), param.getAccountName(), param.getAccountPassword());
    }

    public static Connection tryConnect(OdbcConnectParam param) throws SQLException {
        return pool.getConnection(param.getUrl(), param.getAccountName(), param.getAccountPassword());
    }

    public static void closeConnect(Connection ct, Statement sm, ResultSet rs) {
        if (ct == null && sm == null && rs == null) {

            throw new BizException(DbErrorEnums.PARAM_CLOSE_CONNECT_ERROR);
        }
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (sm != null) {
                sm.close();
                sm = null;
            }
            if (ct != null) {
                ct.close();
                ct = null;
            }

        } catch (SQLException e) {
            return;
        }
    }


    public static List<Field> getTableField(OdbcParam param) throws SQLException {
        Connection ct = connect(param);
        return getTableField(ct, param.getTableName());
    }

    public static List<Field> getTableField(Connection ct, String tableName) throws SQLException {
        List<Field> list = new ArrayList<>();
        DatabaseMetaData dbmd = ct.getMetaData();
        ResultSet rs = dbmd.getColumns(null, null, tableName, OdbcConstants.PERCENTAGE);
        while (rs.next()) {
            Field field = new Field();
            //  列明
            field.setFieldName(rs.getString(TableAttributeEnum.COLUMN_NAME.name()));
            //  列类型
            field.setType(rs.getString(TableAttributeEnum.TYPE_NAME.name()));
            //  列备注
            field.setFieldNote(rs.getString(TableAttributeEnum.REMARKS.name()));
            field.setIsNull(rs.getBoolean(TableAttributeEnum.NULLABLE.name()));
            list.add(field);
        }
        return list;
    }


    public static List<Map<String, Object>> select(OdbcParam param) throws SQLException, ClassNotFoundException {
        Connection ct = connect(param);
        Statement sm = null;
        ResultSet rs = null;
        try {
            String sql = getSelectSql(param.getFields(), param.getSqlStatement());
            log.info("SQL:【{}】", sql);
            sm = ct.createStatement();
            rs = sm.executeQuery(sql);
            return toMap(rs);
        } catch (Exception e) {
            throw new BizException(DbErrorEnums.CLOSE_SELECT_ERROR);
        } finally {
            //closeConnect(ct, sm, rs);
        }
    }


    public static List<Map<String, Object>> toMap(ResultSet rs) throws SQLException {
        if (rs == null) {
            log.info("ResultSet:{}", rs);
            return null;
        }
        List<Map<String, Object>> list = new ArrayList<>();
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount();

        while (rs.next()) {
            Map<String, Object> rowData = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnLabel(i), rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }

    public static String getSelectSql(List<Field> fieldList, String sqlStatement) {
        Set<String> set = fieldList.stream().map(e -> e.getFieldName()).collect(Collectors.toSet());
        String fields = String.join(",", set);
        StringBuffer sb = new StringBuffer(" select ");
        sb.append(fields);
        sb.append(" from ");
        sb.append(" ft_data_collection_content ");
        if (StringUtils.isNotBlank(sqlStatement)) {
            sb.append(" where ");
            sb.append(sqlStatement.replace("where", ""));
        }
        return sb.toString();
    }

    public static Boolean insert(OdbcParam param) throws SQLException, ClassNotFoundException {
        if (CollectionUtils.isEmpty(param.getDatas())) {
            log.info("插入数据为空");
            return false;
        }
        if (param.getFieldMapping() == null) {
            log.info("插入字段为空");
            return false;
        }
        try {
            Connection ct = connect(param);
            PreparedStatement ps = ct.prepareStatement(insertSqlTemplate(param));
            ps = setStatment(ps, param);
            System.out.println(ps.toString());
            ps.executeUpdate();

            return true;
        } catch (Exception e) {
            e.printStackTrace();
            throw new BizException(DbErrorEnums.CLOSE_INSERT_ERROR);
        } finally {
            // closeConnect(ct, sm, rs);
        }
    }

    public static String insertSqlTemplate(OdbcParam param) {
        String sqlTemplate = "INSERT INTO %s (%s) VALUES %s";
        param.getFields().size();
        String dataTemplate = stringOfSize(param.getFields().size(), "?");
        dataTemplate = stringOfSize(param.getDatas().size(), "(" + dataTemplate + ")");
        List<String> f = param.getFields().stream().map(e -> e.getFieldName()).collect(Collectors.toList());
        String fields = String.join(",", f);

        sqlTemplate = String.format(sqlTemplate, param.getTableName(), fields, dataTemplate);
        return sqlTemplate;
    }

    /**
     * 分割字符串
     * @param size
     * @param ch
     * @return
     */
    public static String stringOfSize(int size, String ch) {
        List<String> list = new ArrayList<>();
        while (size-- > 0) {
            list.add(ch);
        }
        return String.join(",", list);
    }

    /**
     * 组装 PreparedStatement
     *
     * @param ps
     * @param param
     * @return
     * @throws SQLException
     */
    public static PreparedStatement setStatment(PreparedStatement ps, OdbcParam param) throws SQLException {
        Object[] source = param.getFieldMapping().keySet().toArray();
        Map<String, Field> fieldMap = param.getFieldMapping();

        for (int j = 0; j < param.getDatas().size(); j++) {
            Map<String, Object> data = param.getDatas().get(j);
            for (int i = 0; i < source.length; i++) {
                Field field = fieldMap.get(source[i]);
                Object obj = data.get(source[i]);
                int num = (j * source.length) + i + 1;
                if (obj == null || field.getFieldName().equals("id")) {
                    ps.setNull(num, Types.INTEGER);
                } else {
                    if (FieldTypeEnum.TIMESTAMP.equals(field.getType())) {
                        ps.setTimestamp(num, (Timestamp) obj);
                    } else if (FieldTypeEnum.BIGINT.equals(field.getType())) {
                        Long value = obj == null ? 0 : (Long) obj;
                        ps.setLong(num, value);
                    } else if (FieldTypeEnum.TINYINT.equals(field.getType())) {
                        int value = obj == null ? 0 : (int) obj;
                        ps.setInt(num, value);
                    } else {
                        ps.setObject(num, obj);
                    }
                }
            }
        }
        return ps;
    }

}
